# -*- coding: utf-8 -*-

# backend/services/data_service.py
"""
数据服务 - 处理数据存储和检索
"""

import sqlite3
import aiosqlite
import json
from typing import List, Dict, Any, Optional
from datetime import datetime
from pathlib import Path
import logging

logger = logging.getLogger(__name__)

class DataService:
    """数据服务类"""
    
    def __init__(self, db_path: str = "data/app.db"):
        self.db_path = Path(db_path)
        self.db_path.parent.mkdir(parents=True, exist_ok=True)
        asyncio.create_task(self._initialize_database())
    
    async def _initialize_database(self):
        """初始化数据库"""
        try:
            async with aiosqlite.connect(self.db_path) as db:
                # 创建对话表
                await db.execute("""
                    CREATE TABLE IF NOT EXISTS conversations (
                        id TEXT PRIMARY KEY,
                        title TEXT NOT NULL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                """)
                
                # 创建消息表
                await db.execute("""
                    CREATE TABLE IF NOT EXISTS messages (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        conversation_id TEXT NOT NULL,
                        role TEXT NOT NULL,
                        content TEXT NOT NULL,
                        metadata TEXT,
                        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (conversation_id) REFERENCES conversations (id)
                    )
                """)
                
                # 创建用户设置表
                await db.execute("""
                    CREATE TABLE IF NOT EXISTS user_settings (
                        key TEXT PRIMARY KEY,
                        value TEXT NOT NULL,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                """)
                
                await db.commit()
                logger.info("数据库初始化完成")
                
        except Exception as e:
            logger.error(f"数据库初始化失败: {e}")
            raise
    
    async def create_conversation(self, conversation_id: str, title: str) -> bool:
        """创建新对话"""
        try:
            async with aiosqlite.connect(self.db_path) as db:
                await db.execute(
                    "INSERT INTO conversations (id, title) VALUES (?, ?)",
                    (conversation_id, title)
                )
                await db.commit()
                logger.info(f"创建对话: {conversation_id}")
                return True
        except Exception as e:
            logger.error(f"创建对话失败: {e}")
            return False
    
    async def save_message(
        self, 
        conversation_id: str, 
        role: str, 
        content: str, 
        metadata: Dict[str, Any] = None
    ) -> bool:
        """保存消息"""
        try:
            async with aiosqlite.connect(self.db_path) as db:
                await db.execute(
                    "INSERT INTO messages (conversation_id, role, content, metadata) VALUES (?, ?, ?, ?)",
                    (conversation_id, role, content, json.dumps(metadata or {}))
                )
                
                # 更新对话的最后更新时间
                await db.execute(
                    "UPDATE conversations SET updated_at = CURRENT_TIMESTAMP WHERE id = ?",
                    (conversation_id,)
                )
                
                await db.commit()
                return True
        except Exception as e:
            logger.error(f"保存消息失败: {e}")
            return False
    
    async def load_conversation(self, conversation_id: str) -> List[Dict[str, Any]]:
        """加载对话历史"""
        try:
            async with aiosqlite.connect(self.db_path) as db:
                async with db.execute(
                    "SELECT role, content, metadata, timestamp FROM messages WHERE conversation_id = ? ORDER BY timestamp",
                    (conversation_id,)
                ) as cursor:
                    messages = []
                    async for row in cursor:
                        messages.append({
                            "role": row[0],
                            "content": row[1],
                            "metadata": json.loads(row[2]) if row[2] else {},
                            "timestamp": row[3]
                        })
                    return messages
        except Exception as e:
            logger.error(f"加载对话失败: {e}")
            return []
    
    async def list_conversations(self) -> List[Dict[str, Any]]:
        """列出所有对话"""
        try:
            async with aiosqlite.connect(self.db_path) as db:
                async with db.execute(
                    "SELECT id, title, created_at, updated_at FROM conversations ORDER BY updated_at DESC"
                ) as cursor:
                    conversations = []
                    async for row in cursor:
                        conversations.append({
                            "id": row[0],
                            "title": row[1],
                            "created_at": row[2],
                            "updated_at": row[3]
                        })
                    return conversations
        except Exception as e:
            logger.error(f"列出对话失败: {e}")
            return []
    
    async def delete_conversation(self, conversation_id: str) -> bool:
        """删除对话"""
        try:
            async with aiosqlite.connect(self.db_path) as db:
                # 删除消息
                await db.execute(
                    "DELETE FROM messages WHERE conversation_id = ?",
                    (conversation_id,)
                )
                
                # 删除对话
                await db.execute(
                    "DELETE FROM conversations WHERE id = ?",
                    (conversation_id,)
                )
                
                await db.commit()
                logger.info(f"删除对话: {conversation_id}")
                return True
        except Exception as e:
            logger.error(f"删除对话失败: {e}")
            return False
    
    async def save_user_setting(self, key: str, value: Any) -> bool:
        """保存用户设置"""
        try:
            async with aiosqlite.connect(self.db_path) as db:
                await db.execute(
                    "INSERT OR REPLACE INTO user_settings (key, value) VALUES (?, ?)",
                    (key, json.dumps(value))
                )
                await db.commit()
                return True
        except Exception as e:
            logger.error(f"保存设置失败: {e}")
            return False
    
    async def load_user_setting(self, key: str, default_value: Any = None) -> Any:
        """加载用户设置"""
        try:
            async with aiosqlite.connect(self.db_path) as db:
                async with db.execute(
                    "SELECT value FROM user_settings WHERE key = ?",
                    (key,)
                ) as cursor:
                    row = await cursor.fetchone()
                    if row:
                        return json.loads(row[0])
                    return default_value
        except Exception as e:
            logger.error(f"加载设置失败: {e}")
            return default_value

